package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import bean.Adress;
import bean.Customer;

/**
 * Handles Customer Requests
 * @author Martin Schophaus
 */
public class DB_Customer extends Handler implements HandlerInterface<Customer> {
	public DB_Customer(Connection db) {
		super(db);
	}
	
	@Override
	public Customer getById(int id) throws SQLException {
		Customer customer = new Customer();
		
		String sql = "SELECT x.customer_id customer_nr, x.name name, x.fname fname, x.adress_id, x.birthday birthday, x.invoice_adress, x.delivery_adress, x.bonus bonus," +
				" adr.adress_id aid, adr.city city, adr.street street, adr.postal_code postal_code, adr.tel tel, adr.tel_mobile tel_mobile, adr.tel_fax tel_fax, adr.email email," +
				" iadr.adress_id iid, iadr.city icity, iadr.street istreet, iadr.postal_code ipostal_code, iadr.tel itel, iadr.tel_mobile itel_mobile, iadr.tel_fax itel_fax, iadr.email iemail," +
				" dadr.adress_id did, dadr.city dcity, dadr.street dstreet, dadr.postal_code dpostal_code, dadr.tel dtel, dadr.tel_mobile dtel_mobile, dadr.tel_fax dtel_fax, dadr.email demail" +
				" FROM customer x" +
				" LEFT JOIN adress adr ON (x.adress_id = adr.adress_id)" +
				" LEFT JOIN adress iadr ON (x.invoice_adress = iadr.adress_id)" +
				" LEFT JOIN adress dadr ON (x.delivery_adress = dadr.adress_id)" +
				" WHERE customer_id = ?";
		PreparedStatement stmt = this.getDb().prepareStatement(sql);
		stmt.setInt(1, id);
		ResultSet result = stmt.executeQuery();
		result.next();
		
		// Read information
		Date birthday = result.getDate("birthday");
		Calendar parsed_birthday = Calendar.getInstance();
		if(birthday != null) {
			parsed_birthday.setTime(birthday);
			customer.setBirthday(parsed_birthday);
		}
		
		customer.setName(result.getString("name"));
		customer.setFname(result.getString("fname"));
		customer.setBonus(result.getDouble("bonus"));
		customer.setCustomer_nr(id);
		
		// Create Adresses
		Adress adress = new Adress(
				result.getString("city"),
				result.getString("postal_code"),
				result.getString("street"),
				result.getString("tel"),
				result.getString("tel_mobile"),
				result.getString("tel_fax"),
				result.getString("email"));
		adress.setId(result.getInt("aid"));
		Adress iadress = new Adress(
				result.getString("icity"),
				result.getString("ipostal_code"),
				result.getString("istreet"),
				result.getString("itel"),
				result.getString("itel_mobile"),
				result.getString("itel_fax"),
				result.getString("iemail"));
		iadress.setId(result.getInt("iid"));
		Adress dadress = new Adress(
				result.getString("dcity"),
				result.getString("dpostal_code"),
				result.getString("dstreet"),
				result.getString("dtel"),
				result.getString("dtel_mobile"),
				result.getString("dtel_fax"),
				result.getString("demail"));
		dadress.setId(result.getInt("did"));
		
		customer.setAdress(adress);
		customer.setInvoice_adress(iadress);
		customer.setDeliver_adress(dadress);
		
		return customer;
	}
	
	@Override
	public ArrayList<Customer> getAll() throws SQLException {
		ArrayList<Customer> customer_list = new ArrayList<Customer>();
		Customer customer;
		String sql = "SELECT x.customer_id customer_nr, x.name name, x.fname fname, x.adress_id, x.birthday birthday, x.invoice_adress, x.delivery_adress, x.bonus bonus," +
				" adr.city city, adr.street street, adr.postal_code postal_code, adr.tel tel, adr.tel_mobile tel_mobile, adr.tel_fax tel_fax, adr.email email," +
				" iadr.city icity, iadr.street istreet, iadr.postal_code ipostal_code, iadr.tel itel, iadr.tel_mobile itel_mobile, iadr.tel_fax itel_fax, iadr.email iemail," +
				" dadr.city dcity, dadr.street dstreet, dadr.postal_code dpostal_code, dadr.tel dtel, dadr.tel_mobile dtel_mobile, dadr.tel_fax dtel_fax, dadr.email demail" +
				" FROM customer x" +
				" JOIN adress adr ON (x.adress_id = adr.adress_id)" +
				" JOIN adress iadr ON (x.invoice_adress = iadr.adress_id)" +
				" JOIN adress dadr ON (x.delivery_adress = dadr.adress_id)" +
				" ORDER BY customer_nr";
		PreparedStatement stmt = this.getDb().prepareStatement(sql);
		ResultSet result = stmt.executeQuery();
		while(result.next()) {
			customer = new Customer();
			// Read information
			Date birthday = result.getDate("birthday");
			Calendar parsed_birthday = Calendar.getInstance();
			if(birthday != null) {
				parsed_birthday.setTime(birthday);
				customer.setBirthday(parsed_birthday);
			}

			customer.setName(result.getString("name"));
			customer.setFname(result.getString("fname"));
			customer.setBonus(result.getDouble("bonus"));
			customer.setCustomer_nr(result.getInt("customer_nr"));
			
			// Create Adresses
			Adress adress = new Adress(
					result.getString("city"),
					result.getString("postal_code"),
					result.getString("street"),
					result.getString("tel"),
					result.getString("tel_mobile"),
					result.getString("tel_fax"),
					result.getString("email"));
			Adress iadress = new Adress(
					result.getString("icity"),
					result.getString("ipostal_code"),
					result.getString("istreet"),
					result.getString("itel"),
					result.getString("itel_mobile"),
					result.getString("itel_fax"),
					result.getString("iemail"));
			Adress dadress = new Adress(
					result.getString("dcity"),
					result.getString("dpostal_code"),
					result.getString("dstreet"),
					result.getString("dtel"),
					result.getString("dtel_mobile"),
					result.getString("dtel_fax"),
					result.getString("demail"));
			
			customer.setAdress(adress);
			customer.setInvoice_adress(iadress);
			customer.setDeliver_adress(dadress);
			
			customer_list.add(customer);
		}
		
		return customer_list;
	}
	
	@Override
	public Customer add(Customer object) throws SQLException {
		Customer customer = object;
		PreparedStatement stmt;
		ResultSet result;
		Adress adress = customer.getAdress();
		Adress invoice_adress = customer.getInvoice_adress();
		Adress delivery_adress = customer.getDeliver_adress();
		String add_adress = "INSERT INTO adress(city, street, postal_code, tel, tel_mobile, tel_fax, email)" +
				"VALUES (?,?,?,?,?,?,?)";
		
		if(adress != null && adress.getId() == -1) {
			stmt = this.getDb().prepareStatement(add_adress, PreparedStatement.RETURN_GENERATED_KEYS);
			stmt.setString(1, adress.getCity());
			stmt.setString(2, adress.getStreet());
			stmt.setString(3, adress.getPostal_cd());
			stmt.setString(4, adress.getTel());
			stmt.setString(5, adress.getTel_mobile());
			stmt.setString(6, adress.getTel_fax());
			stmt.setString(7, adress.getEmail());
			stmt.execute();
			result = stmt.getGeneratedKeys();
			result.next();
			adress.setId(result.getInt(1));
			stmt.close();
		}
		if(adress != null && invoice_adress.getId() == -1){
			stmt = this.getDb().prepareStatement(add_adress, PreparedStatement.RETURN_GENERATED_KEYS);
			stmt.setString(1, invoice_adress.getCity());
			stmt.setString(2, invoice_adress.getStreet());
			stmt.setString(3, invoice_adress.getPostal_cd());
			stmt.setString(4, invoice_adress.getTel());
			stmt.setString(5, invoice_adress.getTel_mobile());
			stmt.setString(6, invoice_adress.getTel_fax());
			stmt.setString(7, invoice_adress.getEmail());
			stmt.execute();
			result = stmt.getGeneratedKeys();
			result.next();
			invoice_adress.setId(result.getInt(1));
			stmt.close();
		}
		if(adress != null && delivery_adress.getId() == -1) {
			stmt = this.getDb().prepareStatement(add_adress, PreparedStatement.RETURN_GENERATED_KEYS);
			stmt.setString(1, delivery_adress.getCity());
			stmt.setString(2, delivery_adress.getStreet());
			stmt.setString(3, delivery_adress.getPostal_cd());
			stmt.setString(4, delivery_adress.getTel());
			stmt.setString(5, delivery_adress.getTel_mobile());
			stmt.setString(6, delivery_adress.getTel_fax());
			stmt.setString(7, delivery_adress.getEmail());
			stmt.execute();
			result = stmt.getGeneratedKeys();
			result.next();
			delivery_adress.setId(result.getInt(1));
			stmt.close();
		}
		
		String sql = "INSERT INTO customer(name, fname, birthday, bonus, adress_id, invoice_adress, delivery_adress)" +
				"VALUES(?,?,?,?,?,?,?)";
		stmt = this.getDb().prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
		stmt.setString(1, customer.getName());
		stmt.setString(2, customer.getFname());
		stmt.setDate(3, new java.sql.Date(customer.getBirthday().getTimeInMillis()));
		stmt.setDouble(4, customer.getBonus());
		if(customer.getAdress() != null) {
			stmt.setInt(5, customer.getAdress().getId());
		}
		if(customer.getInvoice_adress() != null) {
			stmt.setInt(6, customer.getInvoice_adress().getId());
		}
		if(customer.getDeliver_adress() != null) {
			stmt.setInt(7, customer.getDeliver_adress().getId());
		}
		stmt.execute();
		result = stmt.getGeneratedKeys();
		result.next();
		customer.setCustomer_nr(result.getInt(1));
		stmt.close();
		
		return customer;
	}
	
	@Override
	public boolean update(Customer object) throws SQLException {
		Customer customer = object;
		PreparedStatement stmt;
		ResultSet result;
		Adress adress = customer.getAdress();
		Adress invoice_adress = customer.getInvoice_adress();
		Adress delivery_adress = customer.getDeliver_adress();
		String add_adress = "INSERT INTO adress(city, street, postal_code, tel, tel_mobile, tel_fax, email)" +
				"VALUES (?,?,?,?,?,?,?)";
		
		if(adress != null && adress.getId() == -1) {
			stmt = this.getDb().prepareStatement(add_adress, PreparedStatement.RETURN_GENERATED_KEYS);
			stmt.setString(1, adress.getCity());
			stmt.setString(2, adress.getStreet());
			stmt.setString(3, adress.getPostal_cd());
			stmt.setString(4, adress.getTel());
			stmt.setString(5, adress.getTel_mobile());
			stmt.setString(6, adress.getTel_fax());
			stmt.setString(7, adress.getEmail());
			stmt.execute();
			result = stmt.getGeneratedKeys();
			result.next();
			adress.setId(result.getInt(1));
			stmt.close();
		}
		if(adress != null && invoice_adress.getId() == -1){
			stmt = this.getDb().prepareStatement(add_adress, PreparedStatement.RETURN_GENERATED_KEYS);
			stmt.setString(1, invoice_adress.getCity());
			stmt.setString(2, invoice_adress.getStreet());
			stmt.setString(3, invoice_adress.getPostal_cd());
			stmt.setString(4, invoice_adress.getTel());
			stmt.setString(5, invoice_adress.getTel_mobile());
			stmt.setString(6, invoice_adress.getTel_fax());
			stmt.setString(7, invoice_adress.getEmail());
			stmt.execute();
			result = stmt.getGeneratedKeys();
			result.next();
			invoice_adress.setId(result.getInt(1));
			stmt.close();
		}
		if(adress != null && delivery_adress.getId() == -1) {
			stmt = this.getDb().prepareStatement(add_adress, PreparedStatement.RETURN_GENERATED_KEYS);
			stmt.setString(1, delivery_adress.getCity());
			stmt.setString(2, delivery_adress.getStreet());
			stmt.setString(3, delivery_adress.getPostal_cd());
			stmt.setString(4, delivery_adress.getTel());
			stmt.setString(5, delivery_adress.getTel_mobile());
			stmt.setString(6, delivery_adress.getTel_fax());
			stmt.setString(7, delivery_adress.getEmail());
			stmt.execute();
			result = stmt.getGeneratedKeys();
			result.next();
			delivery_adress.setId(result.getInt(1));
			stmt.close();
		}
		
		String sql = "UPDATE customer" +
				" SET name=?, fname=?, birthday=?, bonus=?, adress_id=?, invoice_adress=?, delivery_adress=? WHERE customer_id = ?";
		stmt = this.getDb().prepareStatement(sql);
		stmt.setString(1, customer.getName());
		stmt.setString(2, customer.getFname());
		stmt.setDate(3, new java.sql.Date(customer.getBirthday().getTimeInMillis()));
		stmt.setDouble(4, customer.getBonus());
		if(adress == null) {
			stmt.setNull(5, java.sql.Types.INTEGER);
		} else {
			stmt.setInt(5, adress.getId());
		}
		if(invoice_adress == null) {
			stmt.setNull(6, java.sql.Types.INTEGER);
		} else {
			stmt.setInt(6, invoice_adress.getId());
		}
		if(delivery_adress == null) {
			stmt.setNull(7, java.sql.Types.INTEGER);
		} else {
			stmt.setInt(7, delivery_adress.getId());
		}
		stmt.setInt(8, customer.getCustomer_nr());
		
		int amount = stmt.executeUpdate();
		stmt.close();
		
		return amount>0?true:false;
	}
}